﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Waker.Entitys;
using Waker.Common;
using System.Data.Common;

namespace Waker.DAL
{
    public class DAL_Cards : DAL_Base
    {
        #region 工地
        public DataTable GetAllConstructionSite()
        {
            var dt = base.GetTableBySql("SELECT * FROM T_ConstructionSite WHERE ISNULL(IsDel,0)=0");
            dt.TableName = "T_ConstructionSite";
            return dt;
        }
        public DataTable GetConstructionSite(int id)
        {
            List<En_Param> listParams = new List<En_Param>();
            listParams.Add(new En_Param("Site_ID", DbType.Int32, id));
            var dt = base.GetTableBySql("SELECT * FROM T_ConstructionSite WHERE Site_ID=@Site_ID", listParams);
            dt.TableName = "T_ConstructionSite";
            return dt;
        }
        public bool SaveConstructionSite(DataRow dr)
        {
            if (dr == null)
                return false;
            var sql = string.Empty;
            List<En_Param> listParams = new List<En_Param>();
            listParams.Add(new En_Param("Site_No", DbType.String, dr["Site_No"]));
            listParams.Add(new En_Param("Site_Name", DbType.String, dr["Site_Name"]));
            listParams.Add(new En_Param("Address", DbType.String, dr["Address"]));
            listParams.Add(new En_Param("Site_Contact", DbType.String, dr["Site_Contact"]));
            listParams.Add(new En_Param("Site_Phone", DbType.String, dr["Site_Phone"]));
            listParams.Add(new En_Param("Site_Tel", DbType.String, dr["Site_Tel"]));
            listParams.Add(new En_Param("Remark", DbType.String, dr["Remark"]));
            if (dr["Site_ID"] == DBNull.Value || Convert.ToInt32(dr["Site_ID"]) < 1)
            {
                sql = @"
INSERT INTO	T_ConstructionSite
        ( Site_No ,
          Site_Name ,
          Address ,
          Site_Contact ,
          Site_Phone ,
          Site_Tel ,
          Remark
        )
VALUES  (  @Site_No 
           ,@Site_Name 
           ,@Address
           ,@Site_Contact
           ,@Site_Phone
           ,@Site_Tel
           ,@Remark
        )
";
            }
            else
            {
                sql = @"
UPDATE T_ConstructionSite SET
Site_No=@Site_No 
,Site_Name=@Site_Name 
,Address=@Address
,Site_Contact=@Site_Contact
,Site_Phone=@Site_Phone
,Site_Tel=@Site_Tel
,Remark=@Remark
WHERE Site_ID=@Site_ID
";
                listParams.Add(new En_Param("Site_ID", DbType.Int32, dr["Site_ID"]));
            }
            return base.ExecuteNonQuerySql(sql, listParams) >= 0;
        }
        public bool DeleteConstructionSite(int id)
        {
            var sql = "UPDATE T_ConstructionSite SET IsDel=1,Site_Name=Site_Name+'(已删除)' WHERE Site_ID=@Site_ID";
            List<En_Param> listParams = new List<En_Param>();
            listParams.Add(new En_Param("Site_ID", DbType.Int32, id));
            return base.ExecuteNonQuerySql(sql, listParams) > 0;

        }
        #endregion
        #region 分包商
        public DataTable GetAllDepartment()
        {
            return base.GetTableByProc("P_GetAllDepartment");
        }
        public DataTable GetDepartmentById(string id)
        {
            List<En_Param> listParams = new List<En_Param>();
            listParams.Add(new En_Param("Dept_ID", DbType.String, id));
            var dt = base.GetTableBySql("SELECT * FROM tbDepartment WHERE Dept_ID=@Dept_ID", listParams);
            dt.TableName = "tbDepartment";
            return dt;
        }
        public bool SaveDepartment(DataRow dr)
        {
            if (dr == null)
                return false;
            var sql = string.Empty;
            List<En_Param> listParams = new List<En_Param>();

            listParams.Add(new En_Param("Dept_Name", DbType.String, dr["Dept_Name"]));

            listParams.Add(new En_Param("Dept_TypeId", DbType.Int32, dr["Dept_TypeId"]));
            listParams.Add(new En_Param("Dept_Principal", DbType.String, dr["Dept_Principal"]));

            listParams.Add(new En_Param("Dept_Phone", DbType.String, dr["Dept_Phone"]));
            listParams.Add(new En_Param("Dept_Tel", DbType.String, dr["Dept_Tel"]));
            listParams.Add(new En_Param("Address", DbType.String, dr["Address"]));

            listParams.Add(new En_Param("Email", DbType.String, dr["Email"]));
            listParams.Add(new En_Param("QQ", DbType.String, dr["QQ"]));
            listParams.Add(new En_Param("Site_ID", DbType.Int32, dr["Site_ID"]));

            listParams.Add(new En_Param("Dept_Note", DbType.String, dr["Dept_Note"]));
            var Dept_ID = string.Empty;
            if (dr["Dept_ID"] == DBNull.Value || string.IsNullOrEmpty(dr["Dept_ID"].ToString()))
            {
                Dept_ID = Guid.NewGuid().ToString();
                sql = @"
INSERT INTO	tbDepartment
    ( Dept_ID ,
      Dept_Code,
      Dept_No ,
      Dept_Name ,
      Dept_Note ,
      Dept_TypeId ,
      Dept_Principal ,
      Dept_Phone ,
      Dept_Tel ,
      Address ,
      Email ,
      QQ ,
      Site_ID,
      IsUpLoad
    )
VALUES  ( @Dept_ID ,
      @Dept_Code,
      @Dept_No ,
      @Dept_Name ,
      @Dept_Note ,
      @Dept_TypeId ,
      @Dept_Principal ,
      @Dept_Phone ,
      @Dept_Tel ,
      @Address ,
      @Email ,
      @QQ ,
      @Site_ID,
      @IsUpLoad
    )
";
                listParams.Add(new En_Param("Dept_Code", DbType.String, dr["Dept_Code"]));
            }
            else
            {
                sql = @"
UPDATE [tbDepartment]
SET [Dept_No] = @Dept_No
  ,[Dept_Name] = @Dept_Name
  ,[Dept_Note] = @Dept_Note
  ,[Dept_TypeId] = @Dept_TypeId
  ,[Dept_Principal] = @Dept_Principal
  ,[Dept_Phone] = @Dept_Phone
  ,[Dept_Tel] = @Dept_Tel
  ,[Address] = @Address
  ,[Email] = @Email
  ,[QQ] = @QQ
  ,[Site_ID] = @Site_ID
  ,IsUpLoad=@IsUpLoad
WHERE [Dept_ID] = @Dept_ID
";
                Dept_ID = dr["Dept_ID"].ToString();
            }
            listParams.Add(new En_Param("Dept_ID", DbType.String, Dept_ID));
            listParams.Add(new En_Param("Dept_No", DbType.String, dr["Dept_No"] == DBNull.Value ? GetNextId("tbDepartment", "Dept_No") : Convert.ToInt32(dr["Dept_No"])));
            listParams.Add(new En_Param("IsUpLoad", DbType.String, 0));
            return base.ExecuteNonQuerySql(sql, listParams) >= 0;
        }
        public En_Result DeleteDepartment(string id)
        {
            var count = Convert.ToInt32(base.GetObject("SELECT COUNT(*) FROM tbEmployee WHERE Dept_ID=@Dept_ID AND ISNULL(Emp_IsLeava,0)=1"
                , new En_Param("Dept_ID", DbType.String, id))
                );
            if (count > 0)
                return En_Result.CreateFailedResult("分包商中有员工,请先清空员工后重试");
            List<DbCommand> listCmd = new List<DbCommand>();
            var sql = @"
INSERT INTO tbDeletedDepartment
     ( SerialNumber ,
       Dept_ID ,
       Dept_No ,
       Dept_Name ,
       Dept_Note ,
       Delete_Time ,
       IsUpLoad --,
--       Affirm_Time ,
--       Reserver_Str ,
--       Reserver_Int
     )
    
SELECT   @SerialNumber ,
       Dept_ID ,
       Dept_No ,
       Dept_Name ,
       Dept_Note ,
       GETDATE() ,
       0 --,
--       Affirm_Time ,
--       Reserver_Str ,
--       Reserver_Int
FROM tbDepartment 
WHERE Dept_ID=@Dept_ID
";
            var cmdInset = Db.GetSqlStringCommand(sql);
            Db.AddInParameter(cmdInset, "Dept_ID", DbType.String, id);
            Db.AddInParameter(cmdInset, "SerialNumber", DbType.String, Guid.NewGuid().ToString());
            listCmd.Add(cmdInset);
            var cmdDelete = Db.GetSqlStringCommand("DELETE FROM tbDepartment WHERE Dept_ID=@Dept_ID");
            Db.AddInParameter(cmdDelete, "Dept_ID", DbType.String, id);
            listCmd.Add(cmdDelete);

            var isOk = base.ExecuteNonQuerySqlOnTrans(listCmd);
            if (isOk)
                return En_Result.CreateSuccessResult();
            else
                return En_Result.CreateFailedResult("删除失败");
        }
        #endregion
        #region 人员
        public DataTable EmployeeSeach(string name, string no, string idCard, bool? IsGrantCard, bool isLeave)
        {
            List<En_Param> listParams = new List<En_Param>();
            var sb = new StringBuilder();
            sb.Append(@"
SELECT 
--IsSelect=CONVERT(BIT,0),
IsGrantCard=CONVERT(BIT,(CASE WHEN ISNULL(B.Emp_Card_ID,'')<>'' THEN 1 ELSE 0 END))
,Sex=(CASE WHEN ISNULL(A.Emp_Sex,0)=1 THEN '男' ELSE '女' END) 
,A.Emp_ID ,
A.Dept_ID ,
A.Emp_No ,
A.Emp_Name ,
A.Emp_Sex ,
A.Emp_Address ,
A.Emp_Phone ,
A.Emp_IDCard ,
A.Emp_birthdate ,
A.Emp_Photo ,
A.Emp_InternalID ,
A.Emp_jobID ,
A.Emp_Native ,
A.Emp_nationId ,
A.Emp_eduId ,
A.Emp_Email ,
A.Emp_State ,
A.Emp_IsMaintain ,
A.Emp_IsLeava ,
A.Reserver_Str ,
A.Reserver_Int,
A.IsFingerAny,
A.FingerId,
A.IsUploadFinger,
B.Card_No,
c.Dept_Name,
Job=d.DataName,
Nation=e.Nation_Name,
f.Edu_Name,
WorkTypeName=H.DataName
FROM tbEmployee AS A 
LEFT JOIN tbEmp_Card AS B ON a.Emp_ID=b.Emp_ID
LEFT JOIN tbDepartment AS C ON A.Dept_ID=C.Dept_ID
LEFT JOIN T_BaseData AS D ON A.Emp_jobID=D.ID AND D.DataType='职位'
LEFT JOIN tbEmp_Nation AS E ON A.Emp_nationId=E.Nation_ID 
LEFT JOIN tbEmp_Education AS F ON A.Emp_eduId=F.Edu_ID
LEFT JOIN tbEmp_Native AS G ON A.Emp_Native=g.Native_ID
LEFT JOIN T_BaseData AS H ON A.WorkType=H.ID AND H.DataType='工种'
");

            sb.AppendFormat("WHERE ISNULL(Emp_IsLeava,0)={0}", isLeave ? 1 : 0);
            if (!string.IsNullOrEmpty(name))
            {
                sb.Append(" AND A.Emp_Name=@Emp_Name");
                listParams.Add(new En_Param("Emp_Name", DbType.String, name));
            }
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" AND A.Emp_No=@Emp_No");
                listParams.Add(new En_Param("Emp_No", DbType.String, no));
            }
            if (!string.IsNullOrEmpty(idCard))
            {
                sb.Append(" AND A.Emp_IDCard=@Emp_IDCard");
                listParams.Add(new En_Param("Emp_IDCard", DbType.String, idCard));
            }
            if (IsGrantCard.HasValue)
            {
                sb.AppendFormat(" AND ISNULL(B.Emp_Card_ID,''){0}''", IsGrantCard.Value ? "<>" : "=");
            }
            return base.GetTableBySql(sb.ToString(), listParams);
        }
        public DataTable GetEmployeeByID(string id)
        {
            var dt = base.GetTableBySql("SELECT * FROM tbEmployee WHERE Emp_ID=@Emp_ID", new En_Param("Emp_ID", DbType.String, id));
            if (dt == null)
                return null;
            dt.TableName = "tbEmployee";
            return dt;
        }
        public bool CheckEMployeeNo(string no)
        {
            return Convert.ToInt32(base.GetObject("SELECT COUNT(*) FROM tbEmployee WHERE Emp_No=@Emp_No", new En_Param("Emp_No", DbType.String, no))) < 1;
        }
        public bool CheckEMployeeIDCardNo(string no)
        {
            return Convert.ToInt32(base.GetObject("SELECT COUNT(*) FROM tbEmployee WHERE Emp_IDCard=@Emp_IDCard", new En_Param("Emp_IDCard", DbType.String, no))) < 1;
        }
        public bool SaveEmployee(DataRow dr)
        {
            if (dr == null)
                return false;
            var sql = string.Empty;
            List<DbCommand> listCmd = new List<DbCommand>();
            var id = string.Empty;
            bool IsUploadFinger = false;
            bool isAdd = false;
            if (dr["Emp_ID"] == DBNull.Value || string.IsNullOrEmpty(dr["Emp_ID"].ToString()))
            {
                isAdd = true;
                id = Guid.NewGuid().ToString();
                sql = @"
INSERT INTO tbEmployee
        ( Emp_ID ,
          Dept_ID ,
          Emp_No ,
          Emp_Name ,
          Emp_Sex ,
          Emp_Address ,
          Emp_Phone ,
          Emp_IDCard ,
          Emp_birthdate ,
          Emp_Photo ,
          Emp_InternalID ,
          Emp_jobID ,
          Emp_Native ,
          Emp_nationId ,
          Emp_eduId ,
          WorkType,
          Emp_Email ,
          Emp_State ,
          Emp_IsMaintain,
          FingerId,
          IsUploadFinger,
          FingerPwd,
          CreateDate,CreateUser
        )
VALUES  ( @Emp_ID ,
          @Dept_ID ,
          dbo.GetNextEmpNo(@Dept_ID),
          @Emp_Name ,
          @Emp_Sex ,
          @Emp_Address ,
          @Emp_Phone ,
          @Emp_IDCard ,
          @Emp_birthdate ,
          @Emp_Photo ,
          @Emp_InternalID ,
          @Emp_jobID ,
          @Emp_Native ,
          @Emp_nationId ,
          @Emp_eduId ,
          @WorkType,
          @Emp_Email ,
          @Emp_State ,
          @Emp_IsMaintain,
          @FingerId,
          @IsUploadFinger,
          @FingerPwd,
          Getdate(),@CreateUser
        )
";
            }
            else
            {
                sql = @"
UPDATE  tbEmployee
SET     Dept_ID = @Dept_ID ,
        --Emp_No = @Emp_No ,
        Emp_Name = @Emp_Name ,
        Emp_Sex = @Emp_Sex ,
        Emp_Address = @Emp_Address ,
        Emp_Phone = @Emp_Phone ,
        Emp_IDCard = @Emp_IDCard ,
        Emp_birthdate = @Emp_birthdate ,
        Emp_Photo = @Emp_Photo ,
        Emp_InternalID = @Emp_InternalID ,
        Emp_jobID = @Emp_jobID ,
        Emp_Native = @Emp_Native ,
        Emp_nationId = @Emp_nationId ,
        WorkType=@WorkType,
        Emp_eduId = @Emp_eduId ,
        Emp_Email = @Emp_Email ,
        Emp_State = @Emp_State ,
        Emp_IsMaintain = @Emp_IsMaintain,
        FingerId=@FingerId,
        IsUploadFinger=@IsUploadFinger,
        FingerPwd=@FingerPwd
WHERE   Emp_ID = @Emp_ID 
";
                id = dr["Emp_ID"].ToString();
                IsUploadFinger = Convert.ToBoolean(dr["IsUploadFinger"]);

                var cmdUpdate = base.Db.GetSqlStringCommand("UPDATE tbEmp_Card SET IsUpLoad=0 WHERE Emp_ID=@Emp_ID");
                base.Db.AddInParameter(cmdUpdate, "Emp_ID", DbType.String, id);
                listCmd.Add(cmdUpdate);
            }
            var cmd = base.Db.GetSqlStringCommand(sql);
            base.Db.AddInParameter(cmd, "Emp_ID", DbType.String, id);

            base.Db.AddInParameter(cmd, "Dept_ID", DbType.String, dr["Dept_ID"]);

            //base.Db.AddInParameter(cmd, "Emp_No", DbType.String, emp_No);
            base.Db.AddInParameter(cmd, "Emp_Name", DbType.String, dr["Emp_Name"]);

            base.Db.AddInParameter(cmd, "Emp_Sex", DbType.Byte, dr["Emp_Sex"]);
            base.Db.AddInParameter(cmd, "Emp_Address", DbType.String, dr["Emp_Address"]);
            base.Db.AddInParameter(cmd, "Emp_Phone", DbType.String, dr["Emp_Phone"]);

            base.Db.AddInParameter(cmd, "Emp_IDCard", DbType.String, dr["Emp_IDCard"]);
            base.Db.AddInParameter(cmd, "Emp_birthdate", DbType.DateTime, dr["Emp_birthdate"]);
            base.Db.AddInParameter(cmd, "Emp_Photo", DbType.Binary, dr["Emp_Photo"]);

            base.Db.AddInParameter(cmd, "Emp_InternalID", DbType.Int32, dr["Emp_InternalID"]);

            base.Db.AddInParameter(cmd, "Emp_jobID", DbType.Int32, dr["Emp_jobID"]);
            base.Db.AddInParameter(cmd, "Emp_Native", DbType.String, dr["Emp_Native"]);
            base.Db.AddInParameter(cmd, "Emp_nationId", DbType.Int32, dr["Emp_nationId"]);
            base.Db.AddInParameter(cmd, "Emp_eduId", DbType.Int32, dr["Emp_eduId"]);
            base.Db.AddInParameter(cmd, "WorkType", DbType.Int32, dr["WorkType"]);

            base.Db.AddInParameter(cmd, "Emp_Email", DbType.String, dr["Emp_Email"]);
            base.Db.AddInParameter(cmd, "Emp_State", DbType.Int32, dr["Emp_State"]);
            base.Db.AddInParameter(cmd, "Emp_IsMaintain", DbType.Int32, dr["Emp_IsMaintain"]);

            base.Db.AddInParameter(cmd, "FingerId", DbType.Int32, dr["FingerId"]);
            base.Db.AddInParameter(cmd, "IsUploadFinger", DbType.Boolean, IsUploadFinger);
            base.Db.AddInParameter(cmd, "FingerPwd", DbType.String, dr["FingerPwd"] == DBNull.Value ? string.Empty : dr["FingerPwd"]);
            if (isAdd)
                base.Db.AddInParameter(cmd, "CreateUser", DbType.String, Session._CurrUserCode);
            listCmd.Add(cmd);

            return base.ExecuteNonQuerySqlOnTrans(listCmd);
        }
        public int ImportEmployee(DataTable dt)
        {
            int intRes = 0;
            if (dt == null || dt.Rows.Count < 1)
                return intRes;
            var sql = @"
INSERT INTO tbEmployee
        ( Emp_ID ,
          Dept_ID ,
          Emp_No ,
          Emp_Name ,
          Emp_Sex ,
          Emp_Address ,
          Emp_Phone ,
          Emp_IDCard ,
          Emp_birthdate ,
          Emp_Photo ,
          Emp_jobID ,
          Emp_Native ,
          Emp_nationId ,
          Emp_eduId ,
          WorkType,
          Emp_Email ,
          IsUploadFinger,CreateDate,CreateUser
        )
VALUES  ( @Emp_ID ,
          @Dept_ID ,
          dbo.GetNextEmpNo(@Dept_ID),
          @Emp_Name ,
          @Emp_Sex ,
          @Emp_Address ,
          @Emp_Phone ,
          @Emp_IDCard ,
          @Emp_birthdate ,
          @Emp_Photo ,
          @Emp_jobID ,
          @Emp_Native ,
          @Emp_nationId ,
          @Emp_eduId ,
          @WorkType,
          @Emp_Email ,
          @IsUploadFinger,getdate(),@CreateUser
        )
";


            var cmd = base.Db.GetSqlStringCommand(sql);
            foreach (DataRow dr in dt.Rows)
            {
                List<En_Param> listParams = new List<En_Param>();
                listParams.Add(new En_Param("Emp_ID", DbType.String, Guid.NewGuid().ToString()));

                listParams.Add(new En_Param("Dept_ID", DbType.String, dr["Dept_ID"]));

                //base.Db.AddInParameter(cmd, "Emp_No", DbType.String, emp_No));
                listParams.Add(new En_Param("Emp_Name", DbType.String, dr["姓名"]));

                listParams.Add(new En_Param("Emp_Sex", DbType.Byte, dr["Emp_Sex"]));
                listParams.Add(new En_Param("Emp_Address", DbType.String, !dt.Columns.Contains("地址") || dr["地址"] == DBNull.Value ? string.Empty : dr["地址"]));
                listParams.Add(new En_Param("Emp_Phone", DbType.String, !dt.Columns.Contains("电话") || dr["电话"] == DBNull.Value ? string.Empty : dr["电话"]));

                listParams.Add(new En_Param("Emp_IDCard", DbType.String, !dt.Columns.Contains("身份证号码") || dr["身份证号码"] == DBNull.Value ? string.Empty : dr["身份证号码"]));
                listParams.Add(new En_Param("Emp_birthdate", DbType.DateTime, !dt.Columns.Contains("生日") || dr["生日"] == DBNull.Value ? DBNull.Value : dr["生日"]));
                listParams.Add(new En_Param("Emp_Photo", DbType.Binary, dr["Emp_Photo"]));


                listParams.Add(new En_Param("Emp_Native", DbType.String, !dt.Columns.Contains("籍贯") || dr["籍贯"] == DBNull.Value ? string.Empty : dr["籍贯"]));
                listParams.Add(new En_Param("Emp_jobID", DbType.Int32, dr["Emp_jobID"]));
                listParams.Add(new En_Param("Emp_nationId", DbType.Int32, dr["Emp_nationId"]));
                listParams.Add(new En_Param("Emp_eduId", DbType.Int32, dr["Emp_eduId"]));
                listParams.Add(new En_Param("WorkType", DbType.Int32, dr["WorkType"]));

                listParams.Add(new En_Param("Emp_Email", DbType.String, !dt.Columns.Contains("邮箱") || dr["邮箱"] == DBNull.Value ? string.Empty : dr["邮箱"]));
                listParams.Add(new En_Param("IsUploadFinger", DbType.Boolean, 0));
                listParams.Add(new En_Param("CreateUser", DbType.String, Session._CurrUserCode));

                var isOk = base.ExecuteNonQuerySql(sql, listParams) > 0;
                if (isOk)
                    intRes++;
            }
            return intRes;
        }
        public int GetNextFingerId()
        {
            var sql = "SELECT ISNULL((SELECT MAX(ISNULL(FingerId,0)) FROM tbEmployee),0)";
            return Convert.ToInt32(base.GetObject(sql)) + 1;
        }
        public bool UpdateEmployeeFingerId(string empId, int fingerId)
        {
            return base.ExecuteNonQuerySql(
                "UPDATE tbEmployee SET IsUploadFinger=1,FingerId=@FingerId WHERE Emp_ID=@Emp_ID",
                new En_Param("Emp_ID", DbType.String, empId),
                new En_Param("FingerId", DbType.Int32, fingerId)
                ) > 0;
        }
        public bool UpdateEmployeeFingerIndex(string empId, int index)
        {
            var sql = "UPDATE tbEmployee SET IsFingerAny=1,Finger" + index + "=1 WHERE Emp_ID=@Emp_ID";
            return base.ExecuteNonQuerySql(
              sql,
              new En_Param("Emp_ID", DbType.String, empId)
              ) > 0;
        }
        public DataTable GetEmpCardByEmp_ID(string empId)
        {
            var sql = @"
SELECT 
A.Emp_Name
,B.*
FROM tbEmployee AS A
LEFT JOIN tbEmp_Card  AS B
ON A.Emp_ID=b.Emp_ID
WHERE A.Emp_ID=@Emp_ID";
            return base.GetTableBySql(sql, new En_Param("Emp_ID", DbType.String, empId));
        }

        public bool SaveEmployeeCard(DataRow dr)
        {
            if (dr == null)
                return false;
            var sql = string.Empty;
            List<En_Param> listParams = new List<En_Param>();

            listParams.Add(new En_Param("Emp_ID", DbType.String, dr["Emp_ID"]));

            listParams.Add(new En_Param("Card_No", DbType.String, dr["Card_No"]));
            listParams.Add(new En_Param("Emp_Name", DbType.String, dr["Emp_Name"]));

            listParams.Add(new En_Param("Card_BrushCardCount", DbType.Int32, dr["Card_BrushCardCount"]));
            listParams.Add(new En_Param("Card_ExpireDate", DbType.DateTime, dr["Card_ExpireDate"]));
            listParams.Add(new En_Param("IsUpLoad", DbType.Boolean, 0));

            var id = string.Empty;
            if (dr["Emp_Card_ID"] == DBNull.Value || string.IsNullOrEmpty(dr["Emp_Card_ID"].ToString()))
            {
                listParams.Add(new En_Param("CreateUser", DbType.String, Session._CurrUserCode));
                id = Guid.NewGuid().ToString();
                dr["Card_State"] = 1;
                sql = @" 
INSERT INTO tbEmp_Card
        ( Emp_Card_ID ,
          Emp_ID , 
          Card_No , 
           Card_BrushCardCount ,
          Card_ExpireDate ,
          Card_State,
          IsUpLoad,CreateDate,CreateUser 
        )
VALUES  ( @Emp_Card_ID ,
          @Emp_ID , 
          @Card_No , 
           0 ,
          @Card_ExpireDate ,
          @Card_State,
          @IsUpLoad,getdate(),@CreateUser
        )
";
            }
            else
            {
                sql = @"
UPDATE tbEmp_Card
SET Emp_ID=@Emp_ID , 
  Card_No=@Card_No ,  
  Card_BrushCardCount=@Card_BrushCardCount,
  Card_ExpireDate=@Card_ExpireDate,
  Card_State=@Card_State,
  IsUpLoad=@IsUpLoad 
where Emp_Card_ID=@Emp_Card_ID
";
                id = dr["Emp_Card_ID"].ToString();
            }
            //dr["IsUpLoad"] 
            listParams.Add(new En_Param("Emp_Card_ID", DbType.String, id));
            listParams.Add(new En_Param("Card_State", DbType.Int32, dr["Card_State"]));
            return base.ExecuteNonQuerySql(sql, listParams) >= 0;
        }
        public bool CheckEmployeeCardNo(string no)
        {
            return Convert.ToInt32(base.GetObject("SELECT COUNT(*) FROM tbEmp_Card WHERE Card_No=@Card_No AND Card_State=1", new En_Param("Card_No", DbType.String, no))) < 1;
        }
        public bool SetEmployeeLeave(string empID, bool isLeave)
        {
            var cmdUpdate = base.Db.GetSqlStringCommand("UPDATE tbEmp_Card SET IsUpLoad=0 WHERE Emp_ID=@Emp_ID");
            base.Db.AddInParameter(cmdUpdate, "Emp_ID", DbType.String, empID);

            var cmd = base.Db.GetSqlStringCommand("UPDATE tbEmployee SET Emp_IsLeava=@Emp_IsLeava,IsUploadFinger=0 WHERE Emp_ID=@Emp_ID");
            base.Db.AddInParameter(cmd, "Emp_ID", DbType.String, empID);
            base.Db.AddInParameter(cmd, "Emp_IsLeava", DbType.Boolean, isLeave);

            var cmdDel = base.Db.GetSqlStringCommand("UPDATE  T_DoorPermission SET IsDel=1 WHERE Emp_ID=@Emp_ID");
            base.Db.AddInParameter(cmdDel, "Emp_ID", DbType.String, empID);

            return base.ExecuteNonQuerySqlOnTrans(cmdUpdate, cmd, cmdDel);
        }
        public bool DeleteEmployee(string empID)
        {
            var cmdUpdate = base.Db.GetSqlStringCommand("UPDATE tbEmp_Card SET IsUpLoad=0,Card_State=0 WHERE Emp_ID=@Emp_ID");
            base.Db.AddInParameter(cmdUpdate, "Emp_ID", DbType.String, empID);

            var cmdInsert = base.Db.GetSqlStringCommand(
                @"
INSERT INTO tbDeleted_Employee
( SerialNumber ,
	Emp_ID ,
	Emp_Name ,
	Emp_Sex ,
	Emp_Address ,
	Emp_Phone ,
	Emp_IDCard ,
	Emp_birthdate ,
	Emp_Photo ,
	Emp_InternalID ,
	Delete_Time ,
	Delete_Note ,
	IsUpLoad,
    FingerId,
    IsUploadFinger
)
SELECT 
	@SerialNumber ,
	@Emp_ID ,
	Emp_Name ,
	Emp_Sex ,
	Emp_Address ,
	Emp_Phone ,
	Emp_IDCard ,
	Emp_birthdate ,
	Emp_Photo ,
	Emp_InternalID ,
	GETDATE() ,
	'' ,
	0,
    FingerId,
    0
FROM tbEmployee 
WHERE Emp_ID=@Emp_ID
");
            base.Db.AddInParameter(cmdInsert, "Emp_ID", DbType.String, empID);
            base.Db.AddInParameter(cmdInsert, "SerialNumber", DbType.String, Guid.NewGuid().ToString());

            var cmdDel = base.Db.GetSqlStringCommand("DELETE FROM tbEmployee WHERE Emp_ID=@Emp_ID");
            base.Db.AddInParameter(cmdDel, "Emp_ID", DbType.String, empID);

            var cmdDelDoorPerssion = base.Db.GetSqlStringCommand("UPDATE  T_DoorPermission SET IsDel=1 WHERE Emp_ID=@Emp_ID");
            base.Db.AddInParameter(cmdDelDoorPerssion, "Emp_ID", DbType.String, empID);

            return base.ExecuteNonQuerySqlOnTrans(cmdUpdate, cmdInsert, cmdDel, cmdDelDoorPerssion);
        }
        #endregion
        #region 工资确认
        public En_Result SaveWages(string empId, int year, int month, DateTime fingerTime)
        {
            List<En_Param> listParams = new List<En_Param>();
            listParams.Add(new En_Param("Emp_ID", DbType.String, empId));
            listParams.Add(new En_Param("WagesYear", DbType.Int32, year));
            listParams.Add(new En_Param("WagesMonth", DbType.Int32, month));
            var isOk = Convert.ToInt32(base.GetObject(
                "SELECT COUNT(*) FROM T_Wages WHERE WagesYear=@WagesYear AND WagesMonth=@WagesMonth AND Emp_ID=@Emp_ID",
                listParams)) < 1;
            if (!isOk)
                return En_Result.CreateFailedResult(string.Format("该员工{0}年{1}月工资已经确认,不能重复确认", year, month));
            var sql = @"
 INSERT INTO T_Wages
         ( Emp_ID ,
           WagesYear ,
           WagesMonth ,
           CreteDate ,
           CreateUser ,
           FingerTime
         )
 VALUES  ( @Emp_ID ,
           @WagesYear ,
           @WagesMonth ,
           GETDATE() ,
           @CreateUser ,
           @FingerTime
         )
";
            listParams.Add(new En_Param("CreateUser", DbType.String, Session._CurrUserCode));
            listParams.Add(new En_Param("FingerTime", DbType.DateTime, fingerTime));
            isOk = base.ExecuteNonQuerySql(sql, listParams) > 0;
            if (isOk)
                return En_Result.CreateSuccessResult();
            else
                return En_Result.CreateFailedResult("保存失败");
        }
        public DataTable EmployeeNoWagesSeach(int year, int month, string depID, string name, string no, string idCard)
        {
            List<En_Param> listParams = new List<En_Param>();
            var sb = new StringBuilder();
            sb.Append(@"
SELECT 
IsSelect=CONVERT(BIT,0)
,IsGrantCard=(CASE WHEN ISNULL(B.Emp_Card_ID,'')<>'' THEN 1 ELSE 0 END)
,Sex=(CASE WHEN ISNULL(A.Emp_Sex,0)=1 THEN '男' ELSE '女' END) 
,A.Emp_ID ,
A.Dept_ID ,
A.Emp_No ,
A.Emp_Name ,
A.Emp_Sex ,
A.Emp_Address ,
A.Emp_Phone ,
A.Emp_IDCard ,
A.Emp_birthdate ,
--A.Emp_Photo ,
A.Emp_InternalID ,
A.Emp_jobID ,
A.Emp_Native ,
A.Emp_nationId ,
A.Emp_eduId ,
A.Emp_Email ,
A.Emp_State ,
A.Emp_IsMaintain ,
A.Emp_IsLeava ,
A.Reserver_Str ,
A.Reserver_Int,
A.IsFingerAny,
A.FingerId,
A.IsUploadFinger,
c.Dept_Name,
Job=d.DataName,
Nation=e.Nation_Name,
f.Edu_Name,
WorkTypeName=H.DataName,
LastDate=(SELECT TOP 1 CONVERT(VARCHAR(4),WagesYear)+'年'+CONVERT(VARCHAR(2),WagesMonth)+'月' FROM T_Wages WHERE Emp_ID=A.Emp_ID ORDER BY WagesYear DESC,WagesMonth DESC)
,LastWagesDate=(SELECT TOP 1 FingerTime FROM T_Wages WHERE Emp_ID=A.Emp_ID ORDER BY WagesYear DESC,WagesMonth DESC)
FROM tbEmployee AS A 
LEFT JOIN tbEmp_Card AS B ON a.Emp_ID=b.Emp_ID
LEFT JOIN tbDepartment AS C ON A.Dept_ID=C.Dept_ID
LEFT JOIN T_BaseData AS D ON A.Emp_jobID=D.ID AND D.DataType='职位'
LEFT JOIN tbEmp_Nation AS E ON A.Emp_nationId=E.Nation_ID 
LEFT JOIN tbEmp_Education AS F ON A.Emp_eduId=F.Edu_ID
LEFT JOIN T_BaseData AS H ON A.WorkType=H.ID AND H.DataType='工种'
WHERE ISNULL(Emp_IsLeava,0)=0
AND  NOT  EXISTS (SELECT Emp_ID FROM T_Wages WHERE WagesYear=@WagesYear AND WagesMonth=@WagesMonth AND Emp_ID=A.Emp_ID)
");

            listParams.Add(new En_Param("WagesYear", DbType.Int32, year));
            listParams.Add(new En_Param("WagesMonth", DbType.Int32, month));
            if (!string.IsNullOrEmpty(name))
            {
                sb.Append(" AND A.Emp_Name LIKE '%'+@Emp_Name+'%'");
                listParams.Add(new En_Param("Emp_Name", DbType.String, name));
            }
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" AND A.Emp_No=@Emp_No");
                listParams.Add(new En_Param("Emp_No", DbType.String, no));
            }
            if (!string.IsNullOrEmpty(idCard))
            {
                sb.Append(" AND A.Emp_IDCard=@Emp_IDCard");
                listParams.Add(new En_Param("Emp_IDCard", DbType.String, idCard));
            }
            if (!string.IsNullOrEmpty(depID))
            {
                sb.Append(" AND CHARINDEX(A.Dept_ID+',',@Dept_ID+',')>0");
                listParams.Add(new En_Param("Dept_ID", DbType.String, depID));
            }
            return base.GetTableBySql(sb.ToString(), listParams);
        }
        #endregion

        #region 同步数据
        //        public DataSet GetAllADSDevice()
        //        {
        //            var sql = @"
        //SELECT * FROM tbADS_Device  AS A LEFT JOIN tbADS_ComAdapter AS B ON A.ComAdapter_ID=b.ComAdapter_ID
        //SELECT * FROM tbADS_IO
        //            ";
        //            return base.GetDataSetBySql(sql);
        //        }
        public DataSet GetNoUploadSubContractor()
        {
            var sql = @"
SELECT SerialNumber,Dept_No FROM tbDeletedDepartment WHERE ISNULL(IsUpload,0)=0 AND ISNULL(Dept_No,0)<>0
SELECT Dept_ID,Dept_No FROM tbDepartment WHERE ISNULL(IsUpload,0)=0 OR ISNULL(Dept_No,0)=0
";
            var ds = base.GetDataSetBySql(sql);
            if (ds == null || ds.Tables.Count < 2)
            {
                return null;
            }
            ds.Tables[0].TableName = "tbDeletedDepartment";
            ds.Tables[1].TableName = "tbDepartment";
            return ds;
        }
        public bool SetSubContractorDeletedUpload(string SerialNumber, bool isUpload)
        {
            var sql = "UPDATE tbDeletedDepartment SET IsUpload=@IsUpload WHERE SerialNumber=@SerialNumber";
            return base.ExecuteNonQuerySql(
                sql,
                new En_Param("SerialNumber", DbType.String, SerialNumber),
                //new En_Param("Dept_ID", DbType.String, Dept_ID),
                //new En_Param("Dept_No", DbType.Int32, Dept_No),
                new En_Param("IsUpload", DbType.Boolean, isUpload)
                ) > 0;
        }
        public bool SetSubContractorUpload(string Dept_ID, int Dept_No, bool isUpload)
        {
            var sql = "UPDATE tbDepartment SET Dept_No=@Dept_No,IsUpload=@IsUpload WHERE Dept_ID=@Dept_ID";
            return base.ExecuteNonQuerySql(
                sql,
                new En_Param("Dept_ID", DbType.String, Dept_ID),
                new En_Param("Dept_No", DbType.Int32, Dept_No),
                new En_Param("IsUpload", DbType.Boolean, isUpload)
                ) > 0;
        }

        public DataSet GetNoUploadFingerEmployee()
        {
            var sql = @"
SELECT SerialNumber,Emp_ID,FingerId FROM tbDeleted_Employee WHERE ISNULL(IsUploadFinger,0)=0 AND ISNULL(FingerId,0)<>0
SELECT Emp_ID,FingerId,Emp_Name,FingerPwd FROM tbEmployee WHERE ISNULL(Emp_IsLeava,0)=0 AND (ISNULL(IsUploadFinger,0)=0 OR ISNULL(FingerId,0)=0)
SELECT Emp_ID,FingerId FROM tbEmployee WHERE ISNULL(Emp_IsLeava,0)=1 AND ISNULL(IsUploadFinger,0)=0 AND ISNULL(FingerId,0)<>0          
            ";
            var ds = base.GetDataSetBySql(sql);
            if (ds == null || ds.Tables.Count < 3)
            {
                return null;
            }
            ds.Tables[0].TableName = "tbDeleted_Employee";
            ds.Tables[1].TableName = "tbEmployee";
            ds.Tables[2].TableName = "tbEmployeeLeave";
            return ds;
        }
        public bool SetEmployeeDeletedUploadFinger(string SerialNumber, bool IsUploadFinger)
        {
            return base.ExecuteNonQuerySql(
                "UPDATE tbDeleted_Employee SET IsUploadFinger=@IsUploadFinger WHERE SerialNumber=@SerialNumber",
                new En_Param("SerialNumber", DbType.String, SerialNumber),
                new En_Param("IsUploadFinger", DbType.Boolean, IsUploadFinger)
                ) > 0;
        }
        public bool SetEmployeeUploadFinger(string empID, int fingerId, bool IsUploadFinger)
        {
            return base.ExecuteNonQuerySql(
                "UPDATE tbEmployee SET IsUploadFinger=@IsUploadFinger,FingerId=@FingerId WHERE Emp_ID=@Emp_ID",
                new En_Param("Emp_ID", DbType.String, empID),
                new En_Param("FingerId", DbType.Int32, fingerId),
                new En_Param("IsUploadFinger", DbType.Boolean, IsUploadFinger)
                ) > 0;
        }
        public DataSet GetNoUploadDeviceEmployee()
        {
            var sql = @"
SELECT A.Emp_Card_ID,A.Card_No
FROM tbEmp_Card AS A 
LEFT JOIN tbDeleted_Employee AS B ON A.Emp_ID=B.Emp_ID  
WHERE ISNULL(A.Card_State,0)=0  AND ISNULL(A.IsUpLoad,0)=0  
AND ISNULL(A.Card_No,'')<>'' 
UNION
SELECT A.Emp_Card_ID,A.Card_No
FROM tbEmp_Card AS A 
LEFT JOIN tbEmployee AS B ON A.Emp_ID=B.Emp_ID 
LEFT JOIN tbDepartment AS C ON B.Dept_ID=C.Dept_ID
WHERE ISNULL(A.Card_State,0)=1  AND ISNULL(A.IsUpLoad,0)=0 AND ISNULL(B.Emp_IsLeava,0)=1
AND ISNULL(A.Card_No,'')<>'' AND ISNULL(C.Dept_No,0)>0

SELECT A.Emp_Card_ID,A.Card_No,c.Dept_No,A.Card_ExpireDate,B.Emp_Name,A.Card_Password
FROM tbEmp_Card AS A 
LEFT JOIN tbEmployee AS B ON A.Emp_ID=B.Emp_ID 
LEFT JOIN tbDepartment AS C ON B.Dept_ID=C.Dept_ID
WHERE ISNULL(A.Card_State,0)=1  AND ISNULL(A.IsUpLoad,0)=0 AND ISNULL(B.Emp_IsLeava,0)=0
AND ISNULL(A.Card_No,'')<>'' AND ISNULL(C.Dept_No,0)>0 
            ";
            var ds = base.GetDataSetBySql(sql);
            if (ds == null || ds.Tables.Count < 2)
            {
                return null;
            }
            ds.Tables[0].TableName = "tbEmp_Card_DeletedAndLeave";
            ds.Tables[1].TableName = "tbEmp_Card";
            return ds;
        }
        public DataSet GetNoLoadDoorPerssion()
        {
            return base.GetDataSetByProc("P_GetNoLoadDoorPerssion");
        }
        public bool SetEmployeeCardUploadDevice(string Emp_Card_ID, bool IsUpLoad)
        {
            return base.ExecuteNonQuerySql(
                          "UPDATE tbEmp_Card SET IsUpLoad=@IsUpLoad WHERE Emp_Card_ID=@Emp_Card_ID",
                          new En_Param("Emp_Card_ID", DbType.String, Emp_Card_ID),
                          new En_Param("IsUpLoad", DbType.Boolean, IsUpLoad)
                          ) > 0;
        }
        #endregion

        #region 初始化
        public DataTable GetAllEmployeeAndCard()
        {
            var sql = @"
SELECT   
A.Emp_ID ,
A.Dept_ID ,
A.Emp_No ,
A.Emp_Name ,
c.Dept_Name,
A.Emp_Sex , 
A.Emp_Phone ,
A.Emp_IDCard ,
A.Emp_Photo, 
B.Card_No ,
D.Site_Name,
Job=E.DataName,
WorkTypeName=H.DataName
FROM tbEmployee AS A 
LEFT JOIN tbEmp_Card AS B ON a.Emp_ID=b.Emp_ID
LEFT JOIN tbDepartment AS C ON A.Dept_ID=C.Dept_ID
LEFT JOIN T_ConstructionSite AS D ON C.Site_ID=D.Site_ID
LEFT JOIN T_BaseData AS E ON A.Emp_jobID=E.ID AND E.DataType='职位'
LEFT JOIN T_BaseData AS H ON A.WorkType=H.ID AND H.DataType='工种'
";
            return base.GetTableBySql(sql);
        }
        #endregion

        #region 人员权限
        public DataTable GetDoorPerssion(string deviceID, string depID, string name, string no, string idCard)
        {
            List<En_Param> listParams = new List<En_Param>();
            var sb = new StringBuilder();
            sb.Append(@"
SELECT A.*,
B.Emp_No,
B.Emp_Name,
C.Card_No,
E.Dept_Name,
d.LSubDevice_Name
 FROM T_DoorPermission AS A
LEFT JOIN tbEmployee AS B ON A.Emp_ID=B.Emp_ID
LEFT JOIN tbEmp_Card AS C ON A.Emp_Card_ID=C.Emp_Card_ID
LEFT JOIN tbADS_LSubDevice AS D ON A.LSubDevice_ID=D.LSubDevice_ID
LEFT JOIN tbDepartment AS E ON E.Dept_ID=b.Dept_ID
WHERE ISNULL(A.IsDel,0)=0 AND D.Device_ID=@Device_ID
");

            listParams.Add(new En_Param("Device_ID", DbType.String, deviceID));
            if (!string.IsNullOrEmpty(name))
            {
                sb.Append(" AND B.Emp_Name LIKE '%'+@Emp_Name+'%'");
                listParams.Add(new En_Param("Emp_Name", DbType.String, name));
            }
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" AND B.Emp_No=@Emp_No");
                listParams.Add(new En_Param("Emp_No", DbType.String, no));
            }
            if (!string.IsNullOrEmpty(idCard))
            {
                sb.Append(" AND B.Emp_IDCard=@Emp_IDCard");
                listParams.Add(new En_Param("Emp_IDCard", DbType.String, idCard));
            }
            if (!string.IsNullOrEmpty(depID))
            {
                sb.Append(" AND CHARINDEX(B.Dept_ID+',',@Dept_ID+',')>0");
                listParams.Add(new En_Param("Dept_ID", DbType.String, depID));
            }
            return base.GetTableBySql(sb.ToString(), listParams);
        }
        public DataTable GetDoorPerssionByLSubDevice_ID(string LsubId)
        {
            return base.GetTableByProc("GetDoorPerssionByLSubDevice_ID", new En_Param("LSubDevice_ID", DbType.String, LsubId));
        }
        public bool SaveDoorPermission(List<string> listEmpId, string subId)
        {
            if (string.IsNullOrEmpty(subId.Trim()))
                return false;
            List<DbCommand> listCmd = new List<DbCommand>();
            var cmdDelete = base.Db.GetSqlStringCommand("UPDATE  T_DoorPermission SET IsDel=1 WHERE LSubDevice_ID=@LSubDevice_ID");
            Db.AddInParameter(cmdDelete, "@LSubDevice_ID", DbType.String, subId);
            listCmd.Add(cmdDelete);
            foreach (var code in listEmpId)
            {
                if (string.IsNullOrEmpty(code))
                    continue;
                DbCommand insertCommand = base.Db.GetSqlStringCommand(@"
INSERT INTO T_DoorPermission
        ( LSubDevice_ID ,
          Emp_Card_ID ,
          Emp_ID ,
          IsUpLoad  
        )
SELECT top 1 @LSubDevice_ID ,
        A.Emp_Card_ID ,
          @Emp_ID ,
          0
FROM  tbEmp_Card AS A 
WHERE Emp_ID=@Emp_ID
");
                base.Db.AddInParameter(insertCommand, "@LSubDevice_ID", DbType.String, subId);
                base.Db.AddInParameter(insertCommand, "@Emp_ID", DbType.String, code);
                listCmd.Add(insertCommand);
            }
            return base.ExecuteNonQuerySqlOnTrans(listCmd);
        }
        #endregion

    }
}
